IF OBJECT_ID('fGetSegmentId') IS NOT NULL 
BEGIN 
    DROP FUNCTION fGetSegmentId
    IF OBJECT_ID('fGetSegmentId') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION fGetSegmentId >>>'
    ELSE 
        PRINT '<<< DROPPED FUNCTION fGetSegmentId >>>'
END
GO
CREATE FUNCTION dbo.fGetSegmentId
(
    @SegmentName       varchar(255) = NULL
,   @ParentSegmentName varchar(255) = NULL
)
RETURNS int
AS
BEGIN
    DECLARE
        @SegmentId int

    IF @ParentSegmentName IS NULL
    BEGIN
        SELECT
            @SegmentId = Segment.SegmentId
        FROM
            Segment
        WHERE
            Segment.SegmentName = @SegmentName
        AND
            Segment.ParentSegmentId IS NULL
    END

    IF @ParentSegmentName IS NOT NULL
    BEGIN
        SELECT
            @SegmentId = Segment.SegmentId
        FROM
            Segment
        ,   Segment AS ParentSeg
        WHERE
            Segment.ParentSegmentId = ParentSeg.SegmentId
        AND
            Segment.SegmentName = @SegmentName
        AND
            ParentSeg.SegmentName = @ParentSegmentName
    END

RETURN @SegmentId
END
GO
GRANT EXECUTE ON fGetSegmentId TO public
GO
IF OBJECT_ID('fGetSegmentId') IS NOT NULL 
    PRINT '<<< CREATED FUNCTION fGetSegmentId >>>' 
ELSE 
    PRINT '<<< FAILED CREATING FUNCTION fGetSegmentId >>>' 
GO 